from typing import List, Optional
from fastapi import APIRouter, Depends, HTTPException, status, Query
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, and_, or_, text

from app.core.database import get_db
from app.api.v1.endpoints.auth import get_current_active_user
from app.models.user import User
from app.models.competition import Competition, CompetitionStatus, CompetitionType, DifficultyLevel
from app.schemas.competition import (
    CompetitionCreate, CompetitionUpdate, CompetitionResponse, 
    CompetitionList
)

router = APIRouter()


@router.get("/tags/list", response_model=List[dict])
async def get_competition_tags(db: AsyncSession = Depends(get_db)):
    """获取所有竞赛标签及其对应的竞赛数量，按数量降序排列"""
    
    # 查询所有竞赛的标签
    result = await db.execute(
        select(Competition.tags)
        .where(Competition.tags.isnot(None))
        .where(Competition.tags != "[]")
    )
    
    # 统计标签出现次数
    tag_counts = {}
    for row in result.fetchall():
        tags = row[0]  # tags是JSON数组
        if tags:
            import json
            try:
                tag_list = json.loads(tags) if isinstance(tags, str) else tags
                for tag in tag_list:
                    if tag and tag.strip():
                        tag = tag.strip()
                        tag_counts[tag] = tag_counts.get(tag, 0) + 1
            except (json.JSONDecodeError, TypeError):
                continue
    
    # 按数量降序排列
    sorted_tags = sorted(tag_counts.items(), key=lambda x: x[1], reverse=True)
    
    # 返回格式化的结果
    return [
        {
            "tag": tag,
            "count": count,
            "display_name": tag
        }
        for tag, count in sorted_tags
    ]


@router.get("/")
async def get_competitions(
    search: Optional[str] = Query(None, description="搜索关键词"),
    type: Optional[CompetitionType] = Query(None, description="竞赛类型"),
    category: Optional[str] = Query(None, description="竞赛分类"),
    difficulty: Optional[DifficultyLevel] = Query(None, description="难度等级"),
    platform: Optional[str] = Query(None, description="平台"),
    status: Optional[CompetitionStatus] = Query(None, description="状态"),
    is_featured: Optional[bool] = Query(None, description="是否精选"),
    sort_by: Optional[str] = Query("created_at", description="排序字段: created_at(最新), prize_amount(奖金), participants_count(热度)"),
    sort_order: Optional[str] = Query("desc", description="排序方向: asc(升序), desc(降序)"),
    page: int = Query(1, ge=1, description="页码"),
    size: int = Query(20, ge=1, le=100, description="每页数量"),
    db: AsyncSession = Depends(get_db)
):
    """获取竞赛列表"""
    
    # 构建查询条件
    # 包含is_public为True或NULL的竞赛（NULL视为公开）
    conditions = [or_(Competition.is_public == True, Competition.is_public.is_(None))]
    
    if search:
        search_condition = or_(
            Competition.title.ilike(f"%{search}%"),
            Competition.description.ilike(f"%{search}%"),
            Competition.tags.like(f'%"{search}"%')
        )
        conditions.append(search_condition)
    
    if type:
        conditions.append(Competition.type == type)
    
    if category:
        conditions.append(Competition.category == category)
    
    if difficulty:
        conditions.append(Competition.difficulty == difficulty)
    
    if platform:
        conditions.append(Competition.platform == platform)
    
    if status:
        conditions.append(Competition.status == status)
    
    if is_featured is not None:
        conditions.append(Competition.is_featured == is_featured)
    
    # 执行查询，使用原始字段避免枚举转换错误
    from sqlalchemy import text
    
    # 构建基础查询
    base_query = """
    SELECT id, title, description, short_description, type, category, tags, 
           difficulty, platform, external_url, prize_pool, prize_currency, 
           prize_amount, max_team_size, min_team_size, max_participants, 
           rules, evaluation_criteria, resources, status, participants_count, 
           teams_count, submissions_count, image_url, banner_image, logo_image, 
           gallery_images, is_featured, is_public, view_count, 
           registration_start, registration_end, start_date, end_date, 
           created_at, updated_at
    FROM competitions 
    WHERE (is_public = 1 OR is_public IS NULL)
    """
    
    # 添加额外的筛选条件
    params = {}
    if search:
        base_query += " AND (title LIKE :search OR description LIKE :search OR tags LIKE :search_tag)"
        params['search'] = f"%{search}%"
        params['search_tag'] = f'%"{search}"%'
    
    if type:
        base_query += " AND type = :type"
        params['type'] = type.value
    
    if category:
        base_query += " AND category = :category"
        params['category'] = category
    
    if difficulty:
        base_query += " AND difficulty = :difficulty"
        params['difficulty'] = difficulty.value
    
    if platform:
        base_query += " AND platform = :platform"
        params['platform'] = platform
    
    if status:
        base_query += " AND status = :status"
        params['status'] = status.value
    
    if is_featured is not None:
        base_query += " AND is_featured = :is_featured"
        params['is_featured'] = is_featured
    
    # 先查询总数 - 使用更可靠的方法构建count查询
    count_query = """
    SELECT COUNT(*)
    FROM competitions 
    WHERE (is_public = 1 OR is_public IS NULL)
    """
    
    # 添加相同的筛选条件到count查询
    if search:
        count_query += " AND (title LIKE :search OR description LIKE :search OR tags LIKE :search_tag)"
    
    if type:
        count_query += " AND type = :type"
    
    if category:
        count_query += " AND category = :category"
    
    if difficulty:
        count_query += " AND difficulty = :difficulty"
    
    if platform:
        count_query += " AND platform = :platform"
    
    if status:
        count_query += " AND status = :status"
    
    if is_featured is not None:
        count_query += " AND is_featured = :is_featured"
    
    count_result = await db.execute(text(count_query), params)
    total_count = count_result.scalar()
    
    # 构建动态排序
    # 验证排序字段
    valid_sort_fields = {
        "created_at": "created_at",
        "prize_amount": "prize_amount", 
        "participants_count": "participants_count",
        "start_date": "start_date",
        "end_date": "end_date",
        "title": "title"
    }
    
    # 默认排序字段
    sort_field = valid_sort_fields.get(sort_by, "created_at")
    order_direction = "DESC" if sort_order.lower() == "desc" else "ASC"
    
    # 添加排序和分页
    base_query += f" ORDER BY is_featured DESC, {sort_field} {order_direction}"
    offset = (page - 1) * size
    base_query += f" LIMIT {size} OFFSET {offset}"
    
    result = await db.execute(text(base_query), params)
    competitions = result.fetchall()
    
    # 手动构建响应数据以避免Pydantic验证错误
    competitions_data = []
    for row in competitions:
        # 处理JSON字段
        import json
        try:
            tags = json.loads(row.tags) if row.tags else []
        except:
            tags = []
            
        try:
            resources = json.loads(row.resources) if row.resources else []
        except:
            resources = []
            
        try:
            gallery_images = json.loads(row.gallery_images) if row.gallery_images else []
        except:
            gallery_images = []
        
        competitions_data.append({
            "id": row.id,
            "title": row.title,
            "description": row.description,
            "short_description": row.short_description,
            "type": row.type,
            "category": row.category,
            "tags": tags,
            "difficulty": row.difficulty,
            "platform": row.platform,
            "external_url": row.external_url,
            "prize_pool": row.prize_pool,
            "prize_currency": row.prize_currency,
            "prize_amount": float(row.prize_amount) if row.prize_amount else None,
            "max_team_size": row.max_team_size,
            "min_team_size": row.min_team_size,
            "max_participants": row.max_participants,
            "rules": row.rules,
            "evaluation_criteria": row.evaluation_criteria,
            "resources": resources,
            "status": row.status,
            "participants_count": row.participants_count,
            "teams_count": row.teams_count,
            "submissions_count": row.submissions_count,
            "image_url": row.image_url,
            "banner_image": row.banner_image,
            "logo_image": row.logo_image,
            "gallery_images": gallery_images,
            "is_featured": bool(row.is_featured),
            "is_public": bool(row.is_public) if row.is_public is not None else None,
            "view_count": row.view_count,
            "registration_start": row.registration_start.isoformat() if row.registration_start and hasattr(row.registration_start, 'isoformat') else (row.registration_start if isinstance(row.registration_start, str) else None),
            "registration_end": row.registration_end.isoformat() if row.registration_end and hasattr(row.registration_end, 'isoformat') else (row.registration_end if isinstance(row.registration_end, str) else None),
            "start_date": row.start_date.isoformat() if row.start_date and hasattr(row.start_date, 'isoformat') else (row.start_date if isinstance(row.start_date, str) else None),
            "end_date": row.end_date.isoformat() if row.end_date and hasattr(row.end_date, 'isoformat') else (row.end_date if isinstance(row.end_date, str) else None),
            "created_at": row.created_at.isoformat() if row.created_at and hasattr(row.created_at, 'isoformat') else (row.created_at if isinstance(row.created_at, str) else None),
            "updated_at": row.updated_at.isoformat() if row.updated_at and hasattr(row.updated_at, 'isoformat') else (row.updated_at if isinstance(row.updated_at, str) else None)
        })
    
    # 计算分页信息
    total_pages = (total_count + size - 1) // size  # 向上取整
    has_next = page < total_pages
    has_prev = page > 1
    
    # 返回包含分页信息的结构
    return {
        "items": competitions_data,
        "pagination": {
            "page": page,
            "size": size,
            "total": total_count,
            "total_pages": total_pages,
            "has_next": has_next,
            "has_prev": has_prev
        }
    }


@router.get("/{competition_id}", response_model=CompetitionResponse)
async def get_competition(
    competition_id: int,
    db: AsyncSession = Depends(get_db)
):
    """获取竞赛详情"""
    try:
        # 使用原始SQL查询避免枚举值转换问题
        query = text("""
            SELECT 
                id, title, slug, description, short_description, type, category, tags,
                difficulty, platform, external_url, prize_pool, prize_currency, prize_amount,
                max_team_size, min_team_size, max_participants, rules, evaluation_criteria,
                resources, status, participants_count, teams_count, submissions_count,
                image_url, banner_image, logo_image, gallery_images, is_featured, is_public, view_count,
                registration_start, registration_end, start_date, end_date, created_at, updated_at
            FROM competitions 
            WHERE id = :competition_id
        """)
        
        result = await db.execute(query, {"competition_id": competition_id})
        competition = result.fetchone()
        
        if not competition:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Competition not found"
            )
        
        # 安全处理日期字段
        def safe_isoformat(date_value):
            if date_value is None:
                return None
            if hasattr(date_value, 'isoformat'):
                return date_value.isoformat()
            # 如果是字符串，尝试解析为datetime
            if isinstance(date_value, str):
                try:
                    from datetime import datetime
                    parsed_date = datetime.fromisoformat(date_value.replace('Z', '+00:00'))
                    return parsed_date.isoformat()
                except:
                    return date_value
            return str(date_value)
        
        # 映射数据库值到API枚举值
        def map_type(value):
            if not value:
                return "hackathon"
            mapping = {
                "HACKATHON": "hackathon",
                "DATA_SCIENCE": "data_science",
                "CODING_CHALLENGE": "coding_challenge",
                "DESIGN_CONTEST": "design_contest"
            }
            return mapping.get(value.upper(), "hackathon")
        
        def map_difficulty(value):
            if not value:
                return "beginner"
            mapping = {
                "easy": "beginner",
                "medium": "intermediate", 
                "hard": "advanced",
                "expert": "expert"
            }
            return mapping.get(value.lower(), "beginner")
        
        def map_status(value):
            if not value:
                return "draft"
            mapping = {
                "active": "ongoing",
                "inactive": "draft",
                "completed": "ended",
                "cancelled": "cancelled"
            }
            return mapping.get(value.lower(), "draft")
        
        def parse_json_field(value):
            if not value:
                return []
            if isinstance(value, list):
                return value
            if isinstance(value, str):
                try:
                    import json
                    return json.loads(value)
                except:
                    return []
            return []
        
        # 手动构建响应数据
        return {
            "id": competition.id,
            "title": competition.title,
            "slug": competition.slug or f"competition-{competition.id}",
            "description": competition.description,
            "short_description": competition.short_description,
            "type": map_type(competition.type),
            "category": competition.category,
            "tags": parse_json_field(competition.tags),
            "difficulty": map_difficulty(competition.difficulty),
            "platform": competition.platform,
            "external_url": competition.external_url,
            "prize_pool": competition.prize_pool,
            "prize_currency": competition.prize_currency,
            "prize_amount": competition.prize_amount,
            "max_team_size": competition.max_team_size or 1,
            "min_team_size": competition.min_team_size or 1,
            "max_participants": competition.max_participants,
            "rules": competition.rules,
            "evaluation_criteria": competition.evaluation_criteria,
            "resources": parse_json_field(competition.resources),
            "status": map_status(competition.status),
            "participants_count": competition.participants_count or 0,
            "teams_count": competition.teams_count or 0,
            "submissions_count": competition.submissions_count or 0,
            "banner_image": competition.banner_image,
            "logo_image": competition.logo_image,
            "gallery_images": parse_json_field(competition.gallery_images),
            "is_featured": competition.is_featured if competition.is_featured is not None else False,
            "is_public": competition.is_public if competition.is_public is not None else True,
            "view_count": competition.view_count or 0,
            "registration_start": safe_isoformat(competition.registration_start),
            "registration_end": safe_isoformat(competition.registration_end),
            "start_date": safe_isoformat(competition.start_date),
            "end_date": safe_isoformat(competition.end_date),
            "created_at": safe_isoformat(competition.created_at),
            "updated_at": safe_isoformat(competition.updated_at)
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Internal server error: {str(e)}"
        )


@router.post("/", response_model=CompetitionResponse, status_code=status.HTTP_201_CREATED)
async def create_competition(
    competition_data: CompetitionCreate,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """创建竞赛（仅管理员）"""
    
    if not current_user.is_admin:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only administrators can create competitions"
        )
    
    # 生成slug
    import re
    slug = re.sub(r'[^a-zA-Z0-9\-]', '-', competition_data.title.lower())
    slug = re.sub(r'-+', '-', slug).strip('-')
    
    # 确保slug唯一
    counter = 1
    original_slug = slug
    while True:
        result = await db.execute(select(Competition).where(Competition.slug == slug))
        if not result.scalar_one_or_none():
            break
        slug = f"{original_slug}-{counter}"
        counter += 1
    
    # 创建竞赛
    db_competition = Competition(
        **competition_data.dict(),
        slug=slug,
        status=CompetitionStatus.DRAFT
    )
    
    db.add(db_competition)
    await db.commit()
    await db.refresh(db_competition)
    
    return db_competition


@router.put("/{competition_id}", response_model=CompetitionResponse)
async def update_competition(
    competition_id: int,
    competition_data: CompetitionUpdate,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """更新竞赛（仅管理员）"""
    
    if not current_user.is_admin:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only administrators can update competitions"
        )
    
    # 查找竞赛
    result = await db.execute(select(Competition).where(Competition.id == competition_id))
    competition = result.scalar_one_or_none()
    
    if not competition:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Competition not found"
        )
    
    # 更新字段
    update_data = competition_data.dict(exclude_unset=True)
    for field, value in update_data.items():
        setattr(competition, field, value)
    
    await db.commit()
    await db.refresh(competition)
    
    return competition


@router.delete("/{competition_id}")
async def delete_competition(
    competition_id: int,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """删除竞赛（仅管理员）"""
    
    if not current_user.is_admin:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only administrators can delete competitions"
        )
    
    # 查找竞赛
    result = await db.execute(select(Competition).where(Competition.id == competition_id))
    competition = result.scalar_one_or_none()
    
    if not competition:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Competition not found"
        )
    
    await db.delete(competition)
    await db.commit()
    
    return {"message": "Competition deleted successfully"}


@router.get("/categories/", response_model=List[str])
async def get_competition_categories(db: AsyncSession = Depends(get_db)):
    """获取所有竞赛分类"""
    
    result = await db.execute(
        select(Competition.category)
        .where(Competition.category.isnot(None))
        .distinct()
    )
    categories = [row[0] for row in result.fetchall()]
    
    return categories


@router.get("/platforms/", response_model=List[str])
async def get_competition_platforms(db: AsyncSession = Depends(get_db)):
    """获取所有竞赛平台"""
    
    result = await db.execute(
        select(Competition.platform)
        .where(Competition.platform.isnot(None))
        .distinct()
    )
    platforms = [row[0] for row in result.fetchall()]
    
    return platforms


@router.get("/stats/")
async def get_competition_stats(db: AsyncSession = Depends(get_db)):
    """获取竞赛统计数据"""
    
    # 总竞赛数
    total_result = await db.execute(select(func.count(Competition.id)))
    total_competitions = total_result.scalar()
    
    # 活跃竞赛数
    active_result = await db.execute(
        select(func.count(Competition.id))
        .where(Competition.status == CompetitionStatus.ONGOING)
    )
    active_competitions = active_result.scalar()
    
    # 总参与人数
    participants_result = await db.execute(
        select(func.sum(Competition.participants_count))
    )
    total_participants = participants_result.scalar() or 0
    
    # 总奖金池
    prize_result = await db.execute(
        select(func.sum(Competition.prize_amount))
        .where(Competition.prize_amount.isnot(None))
    )
    total_prize = prize_result.scalar() or 0
    
    return {
        "total_competitions": total_competitions,
        "active_competitions": active_competitions,
        "total_participants": total_participants,
        "total_prize": float(total_prize)
    }